{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "#建立股票数据库\n",
    "\n",
    "\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import tushare as ts\n",
    "import MySQLdb as mdb\n",
    "import matplotlib\n",
    "matplotlib.use('TkAgg')\n",
    "import matplotlib.pyplot as plt\n",
    "%matplotlib inline\n",
    "from matplotlib.collections import LineCollection\n",
    "import pandas as pd\n",
    "from sklearn import cluster,covariance,manifold\n",
    "\n",
    "from matplotlib.font_manager import FontProperties\n",
    "\n",
    "\n",
    "import tushare as ts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "pro = ts.pro_api('1272de974922dba228858095d2c28002a74de242a4b4557d74bdbedc')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 5395 entries, 0 to 5394\n",
      "Data columns (total 10 columns):\n",
      " #   Column        Non-Null Count  Dtype \n",
      "---  ------        --------------  ----- \n",
      " 0   ts_code       5395 non-null   object\n",
      " 1   symbol        5395 non-null   object\n",
      " 2   name          5395 non-null   object\n",
      " 3   area          5380 non-null   object\n",
      " 4   industry      5380 non-null   object\n",
      " 5   cnspell       5395 non-null   object\n",
      " 6   market        5395 non-null   object\n",
      " 7   list_date     5395 non-null   object\n",
      " 8   act_name      2575 non-null   object\n",
      " 9   act_ent_type  2575 non-null   object\n",
      "dtypes: object(10)\n",
      "memory usage: 421.6+ KB\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "ts_code         5395\n",
       "symbol          5395\n",
       "name            5395\n",
       "area            5380\n",
       "industry        5380\n",
       "cnspell         5395\n",
       "market          5395\n",
       "list_date       5395\n",
       "act_name        2575\n",
       "act_ent_type    2575\n",
       "dtype: int64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = pro.query('stock_basic', exchange='', list_status='L')\n",
    "data.info()\n",
    "data.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: sqlalchemy in c:\\users\\liyusen\\appdata\\local\\packages\\pythonsoftwarefoundation.python.3.11_qbz5n2kfra8p0\\localcache\\local-packages\\python311\\site-packages (2.0.37)\n",
      "Requirement already satisfied: greenlet!=0.4.17 in c:\\users\\liyusen\\appdata\\local\\packages\\pythonsoftwarefoundation.python.3.11_qbz5n2kfra8p0\\localcache\\local-packages\\python311\\site-packages (from sqlalchemy) (3.1.1)\n",
      "Requirement already satisfied: typing-extensions>=4.6.0 in c:\\users\\liyusen\\appdata\\local\\packages\\pythonsoftwarefoundation.python.3.11_qbz5n2kfra8p0\\localcache\\local-packages\\python311\\site-packages (from sqlalchemy) (4.12.2)\n",
      "Note: you may need to restart the kernel to use updated packages.\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 5395 entries, 0 to 5394\n",
      "Data columns (total 10 columns):\n",
      " #   Column        Non-Null Count  Dtype \n",
      "---  ------        --------------  ----- \n",
      " 0   ts_code       5395 non-null   object\n",
      " 1   symbol        5395 non-null   object\n",
      " 2   name          5395 non-null   object\n",
      " 3   area          5380 non-null   object\n",
      " 4   industry      5380 non-null   object\n",
      " 5   cnspell       5395 non-null   object\n",
      " 6   market        5395 non-null   object\n",
      " 7   list_date     5395 non-null   object\n",
      " 8   act_name      2575 non-null   object\n",
      " 9   act_ent_type  2575 non-null   object\n",
      "dtypes: object(10)\n",
      "memory usage: 421.6+ KB\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "5395"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%pip install sqlalchemy\n",
    "from sqlalchemy import create_engine\n",
    "import tushare as ts\n",
    "\n",
    "ts.set_token('1272de974922dba228858095d2c28002a74de242a4b4557d74bdbedc')\n",
    "import pandas as pd\n",
    "pro = ts.pro_api()\n",
    "data = pro.query('stock_basic', exchange='', list_status='L')\n",
    "data.info()\n",
    "data.count()\n",
    "engine = create_engine('mysql+mysqldb://root:liyusen222@localhost:3306/stockdb')\n",
    "\n",
    "data.to_sql('stock_basic',engine,if_exists='replace',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 000001.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\liyusen\\AppData\\Local\\Temp\\ipykernel_20968\\99874012.py:18: UserWarning: The provided table name '000001.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock,engine,if_exists='append',index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2 000002.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\liyusen\\AppData\\Local\\Temp\\ipykernel_20968\\99874012.py:18: UserWarning: The provided table name '000002.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock,engine,if_exists='append',index=False)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "3 000004.SZ\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\liyusen\\AppData\\Local\\Temp\\ipykernel_20968\\99874012.py:18: UserWarning: The provided table name '000004.SZ' is not found exactly as such in the database after writing the table, possibly due to case sensitivity issues. Consider using lower case table names.\n",
      "  data_price.to_sql(stock,engine,if_exists='append',index=False)\n"
     ]
    }
   ],
   "source": [
    "import tushare as ts\n",
    "\n",
    "ts.set_token('1272de974922dba228858095d2c28002a74de242a4b4557d74bdbedc')\n",
    "\n",
    "pro = ts.pro_api()\n",
    "stock_list = data['ts_code'].tolist()\n",
    "\n",
    "data_price=[]\n",
    "\n",
    "i=0\n",
    "\n",
    "for stock in data['ts_code'].to_list():\n",
    "    if i==3:\n",
    "        break\n",
    "    i+=1\n",
    "    print(i,stock)\n",
    "    data_price=pro.daily(ts_code=stock)\n",
    "    data_price.to_sql(stock,engine,if_exists='append',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   count(*)\n",
      "0      5395\n"
     ]
    }
   ],
   "source": [
    "sql='select count(*) from stockdb.stock_basic'\n",
    "df=pd.read_sql_query(sql,engine)\n",
    "print(df)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
